create  proc sp_create_update_test_lot_bin_data_entry (
@part_number varchar(50), 
@lot_number varchar(50), 
@bin_number varchar(10),
@bin_qty int,
@vendor_name varchar(50),
@test_type varchar(50) )

as
begin

	declare @process_step_id int
	declare @location_id int
	declare @lot_id int

	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number= @part_number  and PS_Type_Name = 'Test' and PS_Type = @test_type and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
	if @process_step_id is null -- new Product or Product Name mismatch
	begin
		select @process_step_id = Process_Step.ProcessStep_ID from Product_Map_Table,Process_Step where Product_Map_Table.part_number = @part_number and PS_Type_Name='Test' and PS_Type = @test_type and Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID 
		if @process_step_id is null
		begin
			-- dummy update to avoid SQLException
			update Lot_Table set Lot_Number = 'Lot_Number' where Lot_Number = 'Lot_Number'
			return
		end
	end


	select @location_id = Location_ID from Location where L_Type = 'Test' and L_Vendor_Name = @vendor_name
	select  @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	
	if @lot_id is null
	begin
		return
	end
	else
	begin
		if (select count(*) from Test_Bin_Lot_Table where Lot_ID = @lot_id and Bin_Number = @bin_number)  = 0
		begin
			insert into Test_Bin_Lot_Table (Lot_ID, Bin_Number, Quantity, Timestamp) values (@lot_id, @bin_number, @bin_qty, CURRENT_TIMESTAMP)
			
		end
		else
		begin
			update Test_Bin_Lot_Table set Lot_ID = @lot_id, Bin_Number = @bin_number, Quantity = @bin_qty, Timestamp = CURRENT_TIMESTAMP where Lot_ID = @lot_id and Bin_Number = @bin_number
		end
	end

end





